/**
 * 
 */
package com.service.merchant.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.log4j.Logger;

import com.pub.db.DBConnectionManager;
import com.pub.exception.DBException;
import com.pub.exception.NullEnvironmentVariableException;
import com.service.merchant.dao.MerchantDao;
import com.service.merchant.domain.Merchant;
import com.service.merchant.domain.LogMerchantRequestGoogle;
import com.service.merchant.domain.MerchantAttribute;
import com.service.merchant.impl.MerchantImpl;
import com.service.merchant.key.MerchantKey;


/**
 * @author iling
 *
 */
public class MerchantImpl implements MerchantDao
{
	private static Logger logger;
	private DBConnectionManager dbcm;
	
	private String DBTag;
	
	static
	{
		logger = Logger.getLogger(MerchantImpl.class.getName());
	}
	
	/**
	 * 
	 */
	public MerchantImpl(String DBTag) 
	{
		this.DBTag = DBTag;
		
		try {
			dbcm	= DBConnectionManager.getInstance(MerchantImpl.class.getName());
		} catch (NullEnvironmentVariableException e) {
			e.printStackTrace();
		}
	}

	public int insert(Merchant merchant)
	{
		int id = 0;
		
		Connection conn = null;
		Statement sm 	= null;
		ResultSet rs	= null;
		
		StringBuilder sbSql = new StringBuilder(100);
		
		sbSql.append("INSERT INTO merchant SET modifiedTime = UTC_TIMESTAMP(), Operator='auto', Module='");
		
		
		sbSql.append("'");

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();
			
			sm.executeUpdate(sbSql.toString(), Statement.RETURN_GENERATED_KEYS);
			rs = sm.getGeneratedKeys();
			if(rs.next()){
				id = rs.getInt(1);
				
				merchant.setId(id);
			}
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if (rs != null){
	        	try{
	        		rs.close();
				} catch (Exception e){
				}
	        }
			
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("insert method return id value : " + id);
		
		return id;
	}
	
	public int update(Merchant merchant)
	{
		int updateCount = 0;
		
		Connection conn = null;
		Statement sm 	= null;
		
		StringBuilder sbSql = new StringBuilder(100);
		
		sbSql.append("INSERT INTO merchant SET Module='");
		
		
		sbSql.append("' ON DUPLICATE KEY UPDATE Commments=VALUES(Commments), Status=VALUES(Status), orders=VALUES(orders), Module=VALUES(Module), Operator=VALUES(Operator), ModifiedTime=VALUES(ModifiedTime)");

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();
			
			updateCount = sm.executeUpdate(sbSql.toString());
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("update method return update count : " + updateCount);
		
		return updateCount;
	}
	
	public void insertAttributes(List<Merchant> mers) throws DBException
	{
		for(Merchant merchant: mers){
			insertAttributes(merchant.getAttributes());
		}
	}
	
	public void insertAttributes(Map<MerchantKey, MerchantAttribute> tagAttris) throws DBException
	{
		for(Entry<MerchantKey, MerchantAttribute> entry: tagAttris.entrySet()){
			insertAttributes(entry.getValue());
		}
	}
	
	public int insertAttributes(MerchantAttribute tagAttri) throws DBException
	{
		int updateCount = 0;
		
		Connection conn = null;
		Statement sm 	= null;
		
		StringBuilder sbSql = new StringBuilder(100);
		
		sbSql.append("INSERT INTO merchant_attributes SET sourceId=");
		sbSql.append(tagAttri.getPrimaryKey().getSourceId());
		sbSql.append(", merchantId=");
		sbSql.append(tagAttri.getPrimaryKey().getMerchantId());
		sbSql.append(", attributeId=");
		sbSql.append(tagAttri.getPrimaryKey().getAttributeId());
		sbSql.append(", attributeValue='");
		sbSql.append(tagAttri.getAttributeValue());
		
		sbSql.append("' ON DUPLICATE KEY UPDATE attributeValue=VALUES(attributeValue)");

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();
			
			updateCount = sm.executeUpdate(sbSql.toString());
		} catch (Exception e){
			throw new DBException("Error SQL : " + sbSql.toString(), e);
		} finally {
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("update method return update count : " + updateCount);
		
		return updateCount;
	}
	
	public int insertRequestGoogleLog(LogMerchantRequestGoogle tagLog) throws DBException
	{
		int updateCount = 0;
		
		Connection conn = null;
		Statement sm 	= null;
		
		StringBuilder sbSql = new StringBuilder(100);
		
		sbSql.append("INSERT INTO log_merchantRequestGoogle SET merchantId=");
		sbSql.append(tagLog.getMerchantId());
		sbSql.append(", searchCount=");
		sbSql.append(tagLog.getSearchCount());
		sbSql.append(", status=");
		sbSql.append(tagLog.getStatus());
		
		sbSql.append(" ON DUPLICATE KEY UPDATE searchCount= searchCount + 1");

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();
			
			updateCount = sm.executeUpdate(sbSql.toString());
		} catch (Exception e){
			throw new DBException("Error SQL : " + sbSql.toString(), e);
		} finally {
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("update method return update count : " + updateCount);
		
		return updateCount;
	}
	
	@Override
	public Merchant getMerchant(int id) throws DBException
	{
		Merchant rt = null;
		
		Connection 	conn	= null;
		Statement	sm		= null;
		ResultSet	rs		= null;
		
		StringBuilder sbSql = new StringBuilder(100);
		sbSql.append("SELECT Operator, orders, Commments, Status, CreatedTime, ModifiedTime, CountryID, BatchID FROM merchant WHERE ID ='");
		sbSql.append(id);
		sbSql.append("'");
		
        
		try {
			conn 	= dbcm.getConnection(DBTag);
			sm		= conn.createStatement();
			rs		= sm.executeQuery(sbSql.toString());
			if(rs.next()){
				rt = new Merchant();
				
				rt.setId(id);
				
			}
		} catch (Exception e) {
			throw new DBException("Error SQL : " + sbSql.toString(), e);
		}
		     
		return rt;
	}

	@Override
	public List<Merchant> getCustom(String where) throws DBException
	{
		List<Merchant> mappingList	= new LinkedList<Merchant>();
		
		Merchant rt = null;
		
		Connection 	conn	= null;
		Statement	sm		= null;
		ResultSet	rs		= null;
		
		StringBuilder sbSql = new StringBuilder(100);
		sbSql.append("SELECT id, name FROM merchant");
		
		if(where != null)
			sbSql.append(where);
		
		try {
			conn 	= dbcm.getConnection(DBTag);
			sm		= conn.createStatement();
			rs		= sm.executeQuery(sbSql.toString());
			
			while(rs.next()){
				rt = new Merchant();
				
				rt.setId(rs.getInt(1));
				rt.setName(rs.getString(2));
				
				mappingList.add(rt);
			}
		} catch (Exception e) {
			throw new DBException("Error SQL : " + sbSql.toString(), e);
		} finally {
			if(rs != null){
	            try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
        }
		     
		return mappingList;
	}
	
	@Override
	public void close() 
	{
		dbcm.release(MerchantImpl.class.getName());
	}

}
